package com.managementSystem.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.managementSystem.client.StaffAppovalBeanService;
import com.managementSystem.client.StaffApprovalBean;
import com.managementSystem.client.StaffBean;

@SuppressWarnings("serial")
public class StaffApprovalBeanServiceImpl extends RemoteServiceServlet implements StaffAppovalBeanService {
	public void unapproveUser (String ID) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("select * from staffApproval where ID like '" + ID + "';");
			ResultSet result = ps.executeQuery();
			result.first();
			result.deleteRow();
			result.close();
			ps.close();
			
		} catch (Exception e) {

		}
	}

	@Override
	public void approveUser(String ID) {
		StaffApprovalBean approve=getStaffApprovalBean(ID);
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("INSERT into staff " +"(ID, Firstname, Lastname,Username, Password,Email,PrimaryNumber,SecondaryNumber,Warnings,Priority, Type,DaysLate,DaysAbsent) "+"values ('"+approve.getId()+"', '"+approve.getFirstName()+"', '"+approve.getLastName()+"', '"+approve.getUserName()+"', '"+approve.getPassword()+"', '"+approve.getEmail()+"', '"+approve.getPrimaryNumber()+"', '"+approve.getSecondaryNumber()+"', '"+0+"', '"+0+"', '"+0+"', '"+0+"', '"+0+"')");
			ResultSet result = ps.executeQuery();
			result.insertRow();
			result.close();
			ps.close();
			
		} catch (Exception e) {

		}
		
	}

	@Override
	public StaffApprovalBean getStaffApprovalBean(String ID) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("select * from staff where ID like '" + ID + "';");
			ResultSet result = ps.executeQuery();
			StaffApprovalBean approve = new StaffApprovalBean();
			while (result.next()) {
				approve=createBean(result);
			}
			
			result.close();
			ps.close();
			return approve;
			
		} catch (Exception e) {

		}
		return null;
	}
	
	private static StaffApprovalBean createBean(ResultSet result) throws SQLException {
		StaffApprovalBean user=new StaffApprovalBean();
		user.setId(result.getString("ID"));
		user.setFirstName(result.getString("Firstname"));
		user.setLastName(result.getString("Lastname"));
		user.setUserName(result.getString("Username"));
		user.setPassword(result.getString("Password"));
		user.setEmail(result.getString("Email"));
		user.setPrimaryNumber(result.getInt("PrimaryNumber"));
		user.setSecondaryNumber(result.getInt("SecondayNumber"));

		return user;
	}

	@Override
	public void addNewApproval(StaffApprovalBean approvalBean) {
		Connection con = null;
		String url = "jdbc:mysql://SQL09.FREEMYSQL.NET/servicedesk";
		String user = "hari666";
		String pass = "q3procn";

		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			con = DriverManager.getConnection (url,user,pass);
		} catch (Exception e) {
			// no exception
		}
		
		try {
			PreparedStatement ps = con.prepareStatement ("INSERT into staffApproval " +"(ID, Firstname, Lastname,Username, Password,Email,PrimaryNumber,SecondaryNumber) "+"values ('"+approvalBean.getId()+"', '"+approvalBean.getFirstName()+"', '"+approvalBean.getLastName()+"', '"+approvalBean.getUserName()+"', '"+approvalBean.getPassword()+"', '"+approvalBean.getEmail()+"', '"+approvalBean.getPrimaryNumber()+"', '"+approvalBean.getSecondaryNumber()+"')");
			ResultSet result = ps.executeQuery();
			result.insertRow();
			result.close();
			ps.close();
			
		} catch (Exception e) {

		}
		
		
	}
	
}
